﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServerCe;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

// Database Class v.1.1
// by George Makarov
// Email: georgemakarov1@yandex.ru

namespace DatabaseLib
{
    //*****************************************************************
    public enum DatabaseTypes { typeMsSqlServer = 0, typeOracle = 1, typeMsSqlCE = 2 }
    //*****************************************************************
    public enum FieldTypes { n, s, d, u }; //number, string, datetime, uid

    public class ConnectionConfig
    {
        private string _ConnectionString = "";
        private DatabaseTypes _ServerType = DatabaseTypes.typeMsSqlServer;
        //private bool _UseLog = false;
        private string _Server = "";
        private string _Database = "";
        private bool _IntegratedSecurity = true;
        private string _UserName = "";
        private string _Password = "";
        private string _SDFFilename = "";

        public string ConnectionString { get { _Update(); return _ConnectionString; } }
        //public bool UseLog { get { return _UseLog; } set { _UseLog = value; } }
        public string Server { get { return _Server; } set { _Server = value; } }
        public string Database { get { return _Database; } set { _Database = value; } }
        public bool IntegratedSecurity { get { return _IntegratedSecurity; } set { _IntegratedSecurity = value; } }
        public string UserName { get { return _UserName; } set { _UserName = value; } }
        public string Password { get { return _Password; } set { _Password = value; } }
        public string SDFFilename { get { return _SDFFilename; } set { _SDFFilename = value; } }
        public DatabaseTypes ServerType { get { return _ServerType; } }
        //****************************
        private void _Update()
        {
            if ((_ServerType == DatabaseTypes.typeMsSqlServer) || (_ServerType == DatabaseTypes.typeOracle))
            {
                _ConnectionString = "server=" + _Server + ";Database=" + _Database;
                if (_IntegratedSecurity)
                {
                    _ConnectionString += ";Integrated Security = " + IntegratedSecurity.ToString();
                }
                else
                {
                    _ConnectionString += ";User ID = " + _UserName + "; Pwd = " + _Password;
                }
            }
            else
            {
                _ConnectionString = "Data Source = '" + _SDFFilename + "'";
                if (_Password != string.Empty)
                    _ConnectionString += "; Password = '" + _Password + "'";
            }
        }
        //****************************
        public ConnectionConfig(DatabaseTypes pType)
        {
            _ServerType = pType;
        }
    }
    //*****************************************************************
    public class DB
    {
        public delegate void dErrorHandler(Exception ex);
        public event dErrorHandler ErrorHandler;
        public ConnectionConfig _Config;
        //private string LogPath = "SQL.LOG";
        public string NLS_DATEFORMAT = "dmy";
        //****************************
        public DB(ConnectionConfig pConfig)
        {
            _Config = pConfig;
        }
        //****************************
        public void SaveBlobField(string pSql, string pFieldName, MemoryStream pValue)
        {
            try
            {
                if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
                {
                    using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                    {
                        SqlCeTransaction transaction = null;
                        try
                        {
                            con.Open();
                            transaction = con.BeginTransaction();
                            SqlCeCommand cmd = new SqlCeCommand(/*"SET DATEFORMAT " + NLS_DATEFORMAT + "\n" +*/ pSql, con);
                            cmd.Transaction = transaction;
                            cmd.Parameters.Add(pFieldName, SqlDbType.Image);
                            Byte[] aBytes = pValue.ToArray();
                            cmd.Parameters[0].Value = aBytes;
                            cmd.ExecuteNonQuery();
                            transaction.Commit();
                            con.Close();
                        }
                        catch (SqlCeException e)
                        {
                            transaction.Rollback();
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL CE\n" + pSql, e));
                        }
                    }

                }
                else
                {
                    using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                    {
                        SqlTransaction transaction = null;
                        try
                        {
                            con.Open();
                            transaction = con.BeginTransaction();
                            SqlCommand cmd = new SqlCommand("SET DATEFORMAT " + NLS_DATEFORMAT + "\n" + pSql, con);
                            cmd.Transaction = transaction;
                            cmd.Parameters.Add(pFieldName, SqlDbType.Image);
                            Byte[] aBytes = pValue.ToArray();
                            cmd.Parameters[0].Value = aBytes;
                            cmd.ExecuteNonQuery();
                            transaction.Commit();
                            con.Close();
                        }
                        catch (SqlException e)
                        {
                            transaction.Rollback();
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL\n" + pSql, e));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (ErrorHandler != null)
                    ErrorHandler(e);
            }
        }
        //****************************
        public void ExecuteQuery(string pSql)
        {
            try
            {
                if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
                {
                    using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                    {
                        SqlCeTransaction transaction = null;
                        try
                        {
                            con.Open();
                            transaction = con.BeginTransaction();
                            using (SqlCeCommand cmd = new SqlCeCommand(/*"SET DATEFORMAT " + NLS_DATEFORMAT + "\n" +*/ pSql, con))
                            {
                                cmd.Transaction = transaction;
                                cmd.ExecuteNonQuery();
                                transaction.Commit();
                                con.Close();
                            }
                        }
                        catch (SqlCeException e)
                        {
                            transaction.Rollback();
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL CE\n" + pSql, e));
                        }
                    }

                }
                else
                {
                    using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                    {
                        SqlTransaction transaction = null;
                        try
                        {
                            con.Open();
                            transaction = con.BeginTransaction();
                            using (SqlCommand cmd = new SqlCommand("SET DATEFORMAT " + NLS_DATEFORMAT + "\n" + pSql, con))
                            {
                                cmd.Transaction = transaction;
                                cmd.ExecuteNonQuery();
                                transaction.Commit();
                                con.Close();
                            }
                        }
                        catch (SqlException e)
                        {
                            transaction.Rollback();
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL\n" + pSql, e));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (ErrorHandler != null)
                    ErrorHandler(e);
            }
        }
        //****************************
        public void ExecuteQueryBatch(object pSql)
        {
            ExecuteQueryBatch((string[])pSql);
        }

        public void ExecuteQueryBatch(List<string> pSql)
        {
            ExecuteQueryBatch(pSql.ToArray());
        }

        public void ExecuteQueryBatch(string[] pSql)
        {
            if (pSql.Length == 0)
                return;
            try
            {
                if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
                {
                    using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                    {
                        SqlCeTransaction transaction = null;
                        try
                        {
                            con.Open();
                            transaction = con.BeginTransaction();
                            SqlCeCommand cmd = new SqlCeCommand();
                            cmd.Connection = con;
                            cmd.Transaction = transaction;
                            foreach (string sqlQuery in pSql)
                            {
                                cmd.CommandText = sqlQuery;
                                cmd.ExecuteNonQuery();
                            }
                            transaction.Commit();
                            con.Close();
                        }
                        catch (SqlCeException e)
                        {
                            transaction.Rollback();
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL CE\n" + pSql, e));
                        }
                    }

                }
                else
                {
                    using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                    {
                        SqlTransaction transaction = null;
                        try
                        {
                            con.Open();
                            transaction = con.BeginTransaction();
                            SqlCommand cmd = new SqlCommand();
                            cmd.Connection = con;
                            cmd.Transaction = transaction;

                            cmd.CommandText = "SET DATEFORMAT " + NLS_DATEFORMAT;
                            cmd.ExecuteNonQuery();
                            foreach (string sqlQuery in pSql)
                            {
                                cmd.CommandText = sqlQuery;
                                cmd.ExecuteNonQuery();
                            }
                            transaction.Commit();
                            con.Close();
                        }
                        catch (SqlException e)
                        {
                            transaction.Rollback();
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL\n" + pSql, e));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (ErrorHandler != null)
                    ErrorHandler(e);
            }
        }

        //****************************
        public string GetQueryValue(string pSql)
        {
            string aRes = string.Empty;
            try
            {
                if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
                {
                    using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                    {

                        try
                        {
                            con.Open();
                            SqlCeDataAdapter da = new SqlCeDataAdapter(/*"SET DATEFORMAT " + NLS_DATEFORMAT + "\n" +*/ pSql, con);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            DataTable aTable = ds.Tables[0].Copy();
                            if (aTable.Rows.Count == 0)
                            {
                                aRes = "";
                            }
                            else
                            {
                                aRes = aTable.Rows[0][0].ToString();
                            }
                            con.Close();
                        }
                        catch (SqlCeException e)
                        {
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL CE\n" + pSql, e));
                        }

                    }
                }
                else
                {
                    using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                    {

                        try
                        {
                            con.Open();
                            SqlDataAdapter da = new SqlDataAdapter("SET DATEFORMAT " + NLS_DATEFORMAT + "\n" + pSql, con);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            DataTable aTable = ds.Tables[0].Copy();
                            if (aTable.Rows.Count == 0)
                            {
                                aRes = "";
                            }
                            else
                            {
                                aRes = aTable.Rows[0][0].ToString();
                            }
                            con.Close();
                        }
                        catch (SqlException e)
                        {
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL\n" + pSql, e));
                        }

                    }
                }
            }
            catch (Exception e)
            {
                if (ErrorHandler != null)
                    ErrorHandler(e);
            }
            return aRes;
        }
        //****************************
        public object GetQueryObject(string pSql)
        {
            object aRes = null;
            try
            {
                if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
                {
                    using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                    {

                        try
                        {
                            con.Open();
                            SqlCeDataAdapter da = new SqlCeDataAdapter(/*"SET DATEFORMAT " + NLS_DATEFORMAT + "\n" +*/ pSql, con);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            DataTable aTable = ds.Tables[0].Copy();
                            if (aTable.Rows.Count == 0) { aRes = DBNull.Value; }
                            else { aRes = aTable.Rows[0][0]; }
                            con.Close();
                        }
                        catch (SqlCeException e)
                        {
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL CE\n" + pSql, e));
                        }
                    }

                }
                else
                {
                    using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                    {

                        try
                        {
                            con.Open();
                            SqlDataAdapter da = new SqlDataAdapter("SET DATEFORMAT " + NLS_DATEFORMAT + "\n" + pSql, con);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            DataTable aTable = ds.Tables[0].Copy();
                            if (aTable.Rows.Count == 0) { aRes = DBNull.Value; }
                            else { aRes = aTable.Rows[0][0]; }
                            con.Close();
                        }
                        catch (SqlException e)
                        {
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL\n" + pSql, e));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (ErrorHandler != null)
                    ErrorHandler(e);
            }
            return aRes;
        }
        //****************************
        public DataTable GetQueryTable(string pSql)
        {
            DataTable aTable = null;
            try
            {
                if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
                {
                    using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                    {
                        try
                        {
                            con.Open();
                            SqlCeDataAdapter da = new SqlCeDataAdapter(/*"SET DATEFORMAT " + NLS_DATEFORMAT + "\n" +*/ pSql, con);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            aTable = ds.Tables[0].Copy();
                            con.Close();
                        }
                        catch (SqlCeException e)
                        {
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL CE\n" + pSql, e));
                        }
                    }
                }
                else
                {
                    using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                    {
                        try
                        {
                            con.Open();
                            SqlDataAdapter da = new SqlDataAdapter("SET DATEFORMAT " + NLS_DATEFORMAT + "\n" + pSql, con);
                            DataSet ds = new DataSet();
                            da.Fill(ds);
                            aTable = ds.Tables[0].Copy();
                            con.Close();
                        }
                        catch (SqlException e)
                        {
                            if (ErrorHandler != null)
                                ErrorHandler(new Exception("Ошибка СУБД SQL\n" + pSql, e));
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (ErrorHandler != null)
                    ErrorHandler(e);
            }
            return aTable;
        }
        //****************************
        public bool TestConnection()
        {
            bool aRes = true;
            if (_Config.ServerType == DatabaseTypes.typeMsSqlCE)
            {
                using (SqlCeConnection con = new SqlCeConnection(_Config.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        con.Close();
                    }
                    catch
                    {
                        aRes = false;
                    }
                }
            }
            else
            {
                using (SqlConnection con = new SqlConnection(_Config.ConnectionString))
                {
                    try
                    {
                        con.Open();
                        con.Close();
                    }
                    catch
                    {
                        aRes = false;
                    }
                }
            }
            return aRes;
        }
        //****************************
        //Получение Guid записи, если такой записи в таблице нет, создается новая
        //поиск ведется по первому полю из массивов Fields и Values
        public static Guid GetOrInsertID(DataTable DT, FieldTypes FirstFieldType, string IDField, string[] Fields, string[] Values)
        {
            Guid Result = Guid.Empty;
            if (Values[0] == string.Empty)
                return Result;

            string Query;
            switch (FirstFieldType)
            {
                case FieldTypes.n: //если значение числовое
                    {
                        Query = Fields[0] + "=" + Values[0];
                        break;
                    }
                case FieldTypes.d:
                case FieldTypes.u:
                case FieldTypes.s: //если значение строковое, дата или UID
                default:
                    {
                        Query = Fields[0] + "='" + Values[0] + "'";
                        break;
                    }
            }
            //делаем выборку из локальной таблицы
            DataRow[] Rows = DT.Select(Query);
            //если не нашли, то создаем новую запись и возвращаем, иначе возвращаем найденное
            if (Rows.Length == 0)
            {
                Result = Guid.NewGuid();
                DataRow Row = DT.NewRow();
                Row[IDField] = Result;
                for (int i = 0; i < Fields.Length; i++)
                    Row[Fields[i]] = Values[i];
                DT.Rows.Add(Row);
            }
            else
            {
                Result = (Guid)Rows[0][IDField];
            }
            return Result;
        }

        public Guid GetOrInsertID(string TableName, string[] Fields, FieldTypes[] Types, string[] Values, int[] WhereFields)
        {
            Guid Result = Guid.Empty;

            string Query = "Select [" + Fields[0] + "]";
            Query += " from " + TableName+" where ";
            for (int i = 0; i < WhereFields.Length; i++)
            {
                int WhereField = WhereFields[i];
                switch (Types[WhereField])
                {
                    case FieldTypes.n: //если значение числовое
                        {
                            Query += Fields[WhereField] + "=" + Values[WhereField];
                            break;
                        }
                    case FieldTypes.d:
                    case FieldTypes.u:
                    case FieldTypes.s: //если значение строковое, дата или UID
                    default:
                        {
                            Query += Fields[WhereField] + "='" + Values[WhereField] + "'";
                            break;
                        }
                }
                if (i != WhereFields.Length - 1)
                    Query += " AND ";
            }
            object oUID = GetQueryObject(Query);
            //если не нашли, то создаем новую запись и возвращаем, иначе возвращаем найденное
            if (DBNull.Value.Equals(oUID))
            {
                Result = Guid.NewGuid();
                Values[0] = Result.ToString();
                Query = "insert into " + TableName + " (";
                for (int i = 0; i < Fields.Length; i++)
                {
                    Query += "[" + Fields[i] + "]";
                    if (i != Fields.Length - 1)
                        Query += ", ";
                }
                Query += ") values (";
                for (int i = 0; i < Values.Length; i++)
                {
                    switch (Types[0])
                    {
                        case FieldTypes.n: //если значение числовое
                            {
                                Query += Values[i];
                                break;
                            }
                        case FieldTypes.d:
                        case FieldTypes.u:
                        case FieldTypes.s: //если значение строковое, дата или UID
                        default:
                            {
                                Query += "'" + Values[i] + "'";
                                break;
                            }
                    }
                    
                    if (i != Values.Length - 1)
                        Query += ", ";
                }
                Query += ")";
                ExecuteQuery(Query);
            }
            else
            {
                Result = (Guid)oUID;
            }
            return Result;
        }

    }
}
